Load Libraries

require(tidyverse)
## Loading required package: tidyverse
## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --
## v ggplot2 3.3.5     v purrr   0.3.4
## v tibble  3.1.5     v dplyr   1.0.7
## v tidyr   1.1.4     v stringr 1.4.0
## v readr   2.0.2     v forcats 0.5.1
## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()
require(lubridate)
## Loading required package: lubridate
## 
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
## 
##     date, intersect, setdiff, union
require(stringr)
require(readxl)
## Loading required package: readxl
require(plotly)
## Loading required package: plotly
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout

Load Call Trends Yearly files

CEMonthly_2019 <- read.csv("../Data/Cleaned/CEMonthly_2019.csv")
CEMonthly_2020 <- read.csv("../Data/Cleaned/CEMonthly_2020.csv")
CEMonthly_2021 <- read.csv("../Data/Cleaned/CEMonthly_2021.csv")

Type Covert

CEMonthly_2019 <- CEMonthly_2019 %>% mutate_all(type.convert)
## Warning in type.convert.default(DATE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALLS): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(AGENT.COUNT): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(REPORTING.ABANDON_DISPOSITION.COUNT): 'as.is'
## should be specified by the caller; using TRUE
## Warning in type.convert.default(ABANDONED.COUNT): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(ABANDONED.REC): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HANDLE.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.SPEED.OF.ANSWER): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.QUEUE.WAIT.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HOLD.TIME): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(SERVICE.LEVEL.REC): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(FILE_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(SHEET_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALL_TYPE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(LANGUAGE): 'as.is' should be specified by the
## caller; using TRUE
CEMonthly_2019$DATE <- ymd(CEMonthly_2019$DATE)

CEMonthly_2020 <- CEMonthly_2020 %>% mutate_all(type.convert)
## Warning in type.convert.default(DATE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALLS): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(AGENT.COUNT): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(REPORTING.ABANDON_DISPOSITION.COUNT): 'as.is'
## should be specified by the caller; using TRUE
## Warning in type.convert.default(ABANDONED.COUNT): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(ABANDONED.REC): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HANDLE.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.SPEED.OF.ANSWER): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.QUEUE.WAIT.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HOLD.TIME): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(SERVICE.LEVEL.REC): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(FILE_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(SHEET_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALL_TYPE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(LANGUAGE): 'as.is' should be specified by the
## caller; using TRUE
CEMonthly_2020$DATE <- ymd(CEMonthly_2020$DATE)

CEMonthly_2021 <- CEMonthly_2021 %>% mutate_all(type.convert)
## Warning in type.convert.default(DATE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALLS): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(AGENT.COUNT): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(REPORTING.ABANDON_DISPOSITION.COUNT): 'as.is'
## should be specified by the caller; using TRUE
## Warning in type.convert.default(ABANDONED.COUNT): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(ABANDONED.REC): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HANDLE.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.SPEED.OF.ANSWER): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.QUEUE.WAIT.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HOLD.TIME): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(SERVICE.LEVEL.REC): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(FILE_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(SHEET_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALL_TYPE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(LANGUAGE): 'as.is' should be specified by the
## caller; using TRUE
CEMonthly_2021$DATE <- ymd(CEMonthly_2021$DATE)

Align columns by names

CEMonthly_2020 <- CEMonthly_2020 %>% select(colnames(CEMonthly_2019))
CEMonthly_2021 <- CEMonthly_2021 %>% select(colnames(CEMonthly_2019))

Bind all Dataframes

CEMonthly <- rbind(CEMonthly_2019,CEMonthly_2020,CEMonthly_2021)

Convert wait times to seconds

CEMonthly <- CEMonthly %>% mutate(AVERAGE.HANDLE.TIME = if_else(is.na(AVERAGE.HANDLE.TIME),"00:00:00",AVERAGE.HANDLE.TIME)
                     ,AVERAGE.SPEED.OF.ANSWER = if_else(is.na(AVERAGE.SPEED.OF.ANSWER),"00:00:00",AVERAGE.SPEED.OF.ANSWER)
                     ,AVERAGE.QUEUE.WAIT.TIME = if_else(is.na(AVERAGE.QUEUE.WAIT.TIME),"00:00:00",AVERAGE.QUEUE.WAIT.TIME)
                     ,AVERAGE.HOLD.TIME = if_else(is.na(AVERAGE.HOLD.TIME),"00:00:00",AVERAGE.HOLD.TIME))

CEMonthly <- CEMonthly %>% mutate(AVERAGE.HANDLE.TIME = if_else(AVERAGE.HANDLE.TIME == '-',"00:00:00",AVERAGE.HANDLE.TIME)
                     ,AVERAGE.SPEED.OF.ANSWER = if_else(AVERAGE.SPEED.OF.ANSWER == '-',"00:00:00",AVERAGE.SPEED.OF.ANSWER)
                     ,AVERAGE.QUEUE.WAIT.TIME = if_else(AVERAGE.QUEUE.WAIT.TIME == '-',"00:00:00",AVERAGE.QUEUE.WAIT.TIME)
                     ,AVERAGE.HOLD.TIME = if_else(AVERAGE.HOLD.TIME == '-',"00:00:00",AVERAGE.HOLD.TIME))

CEMonthly <- CEMonthly %>% mutate(AVERAGE.HANDLE.TIME = as.integer(as.difftime(AVERAGE.HANDLE.TIME))
                     ,AVERAGE.SPEED.OF.ANSWER = as.integer(as.difftime(AVERAGE.SPEED.OF.ANSWER))
                     ,AVERAGE.QUEUE.WAIT.TIME = as.integer(as.difftime(AVERAGE.QUEUE.WAIT.TIME))
                     ,AVERAGE.HOLD.TIME = as.integer(as.difftime(AVERAGE.HOLD.TIME)))

Make dataframe ready for plot

CEMonthly <- CEMonthly %>% mutate(MONTH = month(DATE),YEAR = year(DATE))
CEMonthly_Avg <- CEMonthly %>% group_by(YEAR,MONTH) %>% summarise(AVERAGE.QUEUE.WAIT.TIME = mean(AVERAGE.QUEUE.WAIT.TIME)
                                                                  ,AVERAGE.HOLD.TIME = mean(AVERAGE.HOLD.TIME)) %>% ungroup()
## `summarise()` has grouped output by 'YEAR'. You can override using the `.groups` argument.
CEMonthly_plot <- CEMonthly %>% select(MONTH) %>% distinct() %>% left_join(CEMonthly_Avg %>% filter(YEAR == 2019) %>% select(MONTH,AVERAGE.QUEUE.WAIT.TIME_2019 = AVERAGE.QUEUE.WAIT.TIME, AVERAGE.HOLD.TIME_2019 = AVERAGE.HOLD.TIME),by = 'MONTH') %>% left_join(CEMonthly_Avg %>% filter(YEAR == 2020) %>% select(MONTH,AVERAGE.QUEUE.WAIT.TIME_2020 = AVERAGE.QUEUE.WAIT.TIME, AVERAGE.HOLD.TIME_2020 = AVERAGE.HOLD.TIME),by = 'MONTH') %>% left_join(CEMonthly_Avg %>% filter(YEAR == 2021) %>% select(MONTH,AVERAGE.QUEUE.WAIT.TIME_2021 = AVERAGE.QUEUE.WAIT.TIME, AVERAGE.HOLD.TIME_2021 = AVERAGE.HOLD.TIME),by = 'MONTH')

CEMonthly_plot <- CEMonthly_plot %>% mutate(MONTH__TXT = month.abb[MONTH]) %>% arrange(MONTH)

CEMonthly_plot$MONTH__TXT <- factor(CEMonthly_plot$MONTH__TXT, levels = CEMonthly_plot[["MONTH__TXT"]])

Plot - Average Wait times

fig <- plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.QUEUE.WAIT.TIME_2019, name = 'AVERAGE.QUEUE.WAIT.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4)) 

fig <- fig %>% add_trace(y = ~AVERAGE.QUEUE.WAIT.TIME_2020, name = 'AVERAGE.QUEUE.WAIT.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4)) 

fig <- fig %>% add_trace(y = ~AVERAGE.QUEUE.WAIT.TIME_2021, name = 'AVERAGE.QUEUE.WAIT.TIME_2021', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4)) 

fig <- fig %>% layout(title = "Average Queue wait times",
         xaxis = list(title = "Months"),
         yaxis = list (title = "Time in seconds"))

fig
subplot(
        plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.QUEUE.WAIT.TIME_2019, name = 'AVERAGE.QUEUE.WAIT.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4)),
            plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.QUEUE.WAIT.TIME_2020, name = 'AVERAGE.QUEUE.WAIT.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4))
        , plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.QUEUE.WAIT.TIME_2021, name = 'AVERAGE.QUEUE.WAIT.TIME_2021', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4)) %>%
            layout(title = "Average Queue Wait Times",
         xaxis = list(title = "Months"),
         yaxis = list (title = "Time in seconds")),shareX = TRUE, shareY = TRUE)

Plot - Average hold times

fig <- plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.HOLD.TIME_2019, name = 'AVERAGE.HOLD.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4)) 

fig <- fig %>% add_trace(y = ~AVERAGE.HOLD.TIME_2020, name = 'AVERAGE.HOLD.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4)) 

fig <- fig %>% add_trace(y = ~AVERAGE.HOLD.TIME_2021, name = 'AVERAGE.HOLD.TIME_2021', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4)) 

fig <- fig %>% layout(title = "Average hold times",
         xaxis = list(title = "Months"),
         yaxis = list (title = "Time in seconds"))

fig
subplot(
        plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.HOLD.TIME_2019, name = 'AVERAGE.HOLD.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4)),
            plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.HOLD.TIME_2020, name = 'AVERAGE.HOLD.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4))
        , plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.HOLD.TIME_2021, name = 'AVERAGE.HOLD.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4)) %>%
            layout(title = "Average hold times",
         xaxis = list(title = "Months"),
         yaxis = list (title = "Time in seconds")),shareX = TRUE, shareY = TRUE)